﻿using Kingdee.BOS;
using Kingdee.BOS.App.Data;
using Kingdee.BOS.Core.DynamicForm.PlugIn;
using Kingdee.BOS.Core.DynamicForm.PlugIn.Args;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;

namespace Kingdee.Bos.Project.DyForm
{
    [Description("【服务插件】价格调整单审核，判断是否存在重复物料，然后修改价目表对应值")]
    [HotUpdate]

    public class JiaGeTiaoZhengShenHe : AbstractOperationServicePlugIn
    {

        public override void OnPreparePropertys(PreparePropertysEventArgs e)
        {
            base.OnPreparePropertys(e);
            // 添加预加载字段
            e.FieldKeys.Add("BillNo");
        }

        public override void EndOperationTransaction(EndOperationTransactionArgs e)
        {
            base.EndOperationTransaction(e);
            foreach (DynamicObject item in e.DataEntitys)
            {
                var FBillNo = item["BillNo"].ToString();//编号

                //修改【销售价目表】。价格=【价格调整单】。调整后价格
                //获取数据库相应数据
                string SQLselect = @"select FBILLNO AS '单据编号',F_VHTP_WLMC AS '物料', 
                       F_VHTP_JM1 AS '价目表1',F_VHTP_DJ11 AS '价目表1调前含税单价',F_VHTP_DJ12 AS '价目表1调后含税单价', 
                       F_VHTP_JM2 AS '价目表2',F_VHTP_DJ21 AS '价目表2调前含税单价',F_VHTP_DJ22 AS '价目表2调后含税单价', 
                       F_VHTP_JM3 AS '价目表3',F_VHTP_DJ31 AS '价目表3调前含税单价',F_VHTP_DJ32 AS '价目表3调后含税单价', 
                       F_VHTP_JM4 AS '价目表4',F_VHTP_DJ41 AS '价目表4调前含税单价',F_VHTP_DJ42 AS '价目表4调后含税单价', 
                       F_VHTP_JM5 AS '价目表5',F_VHTP_DJ51 AS '价目表5调前含税单价',F_VHTP_DJ52 AS '价目表5调后含税单价', 
                       F_VHTP_JM6 AS '价目表6',F_VHTP_DJ61 AS '价目表6调前含税单价',F_VHTP_DJ62 AS '价目表6调后含税单价', 
                       F_VHTP_JM7 AS '价目表7',F_VHTP_DJ71 AS '价目表7调前含税单价',F_VHTP_DJ72 AS '价目表7调后含税单价', 
                       F_VHTP_JM8 AS '价目表8',F_VHTP_DJ81 AS '价目表8调前含税单价',F_VHTP_DJ82 AS '价目表8调后含税单价', 
                       F_VHTP_JM9 AS '价目表9',F_VHTP_DJ91 AS '价目表9调前含税单价',F_VHTP_DJ92 AS '价目表9调后含税单价', 
                       F_VHTP_JM10 AS '价目表10',F_VHTP_DJ101 AS '价目表10调前含税单价',F_VHTP_DJ102 AS '价目表10调后含税单价' 
                       FROM VHTP_t_Cust100006 T1 
                       LEFT JOIN VHTP_t_Cust_Entry100002 T2 
                       ON T2.FID = T1.FID 
                       where FBILLNO = '" + FBillNo + "' ";
                DataTable dt1;
                dt1 = DBUtils.ExecuteDataSet(this.Context, SQLselect).Tables[0];


                //生效日期 : EffectiveDate
                string SQLselectEffectiveDate = @"select F_VHTP_DATE 
                        FROM VHTP_t_Cust100006 T1 
                        LEFT JOIN VHTP_t_Cust_Entry100002 T2 
                        ON T2.FID = T1.FID 
                        where FBILLNO = '" + FBillNo + "' ";
                DataTable dtEffectiveDate;
                dtEffectiveDate = DBUtils.ExecuteDataSet(this.Context, SQLselectEffectiveDate).Tables[0];
                string EffectiveDate = dtEffectiveDate.Rows[0][0].ToString();


                //数据库获取数据判断是否存在重复值
                string SQLselect2 = @"SELECT COUNT(F_VHTP_WLMC),F_VHTP_WLMC 
                    from VHTP_t_Cust100006 T1 LEFT JOIN VHTP_t_Cust_Entry100002 T2 ON T2.FID=T1.FID  
                    where FBILLNO= '" + FBillNo + "' group by F_VHTP_WLMC  having COUNT(F_VHTP_WLMC) >1";

                var dt2 = DBUtils.ExecuteDynamicObject(this.Context, SQLselect2);

                //循环dt2 判断是否存在重复物料 若存在 改变PuanDuanZhi 
                string PuanDuanZhi = "true";

                if (dt2.Count > 0)
                {
                    PuanDuanZhi = "false";
                }
                //PuanDuanZhi
                //不存在重复物料
                if (PuanDuanZhi == "true")
                {

                    if (dt1.Rows.Count > 0)
                    {

                        //修改第i+1行物料 对应价目表的数据
                        for (int i = 0; i < dt1.Rows.Count; i++)
                        {
                            //获取各字段值
                            //共32个字段   
                            String FBILLNO = dt1.Rows[i][0].ToString();
                            string FMATERIALID = dt1.Rows[i][1].ToString();

                            //判断第i+1行物料是否存在重复价目表 
                            string SQLselect3 = $@"/*dialect*/select FEntryID, jmbid,count(1) qty from (
 select t.FEntryID,t.jmbid,t.jmb from (
 select * from (
 select t2.FEntryID, F_VHTP_JM1 ,  F_VHTP_JM2 ,  F_VHTP_JM3 , F_VHTP_JM4 , 
                                  F_VHTP_JM5 , F_VHTP_JM6 ,  F_VHTP_JM7 ,  F_VHTP_JM8 , 
                                  F_VHTP_JM9 ,  F_VHTP_JM10  FROM VHTP_t_Cust100006 T1  LEFT JOIN VHTP_t_Cust_Entry100002 T2  
                                  ON T2.FID = T1.FID  where FBILLNO = '{FBillNo}'
) t
UNPIVOT (jmbid for jmb in(F_VHTP_JM1 ,  F_VHTP_JM2 ,  F_VHTP_JM3 , F_VHTP_JM4 , 
                                  F_VHTP_JM5 , F_VHTP_JM6 ,  F_VHTP_JM7 ,  F_VHTP_JM8 , 
                                  F_VHTP_JM9 ,  F_VHTP_JM10) )AS UnpivotTable
								  ) t
where t.jmbid!=0
) a
group by FEntryID, jmbid
having count(1) > 1";

                            int PanDuanZhi2 = 0;
                            var dt3 = DBUtils.ExecuteDynamicObject(this.Context, SQLselect3);

                            if (dt3.Count > 0)
                            {
                                PanDuanZhi2 = 1;
                            }
                            if (PanDuanZhi2 == 1)
                            {

                                throw new KDBusinessException("", "第" + (i + 1) + "行存在重复价目表！");
                            }
                            else
                            {
                                //修改各个价目表数据
                                for (int r = 1; r <= 10; r++)
                                {
                                    ////修改第i+1行物料 对应价目表的数据
                                    string JMID = dt1.Rows[i][3 * r - 1].ToString();
                                    string oldFPRICE = dt1.Rows[i][3 * r].ToString();
                                    string newFPRICE = dt1.Rows[i][3 * r + 1].ToString();

                                    if (JMID != "0")
                                    {
                                        if (double.Parse(newFPRICE) > 0)
                                        {
                                            //判断价目表是否存在该数据
                                            string SQLselect4 = " select * from T_SAL_PRICELISTENTRY where FID = '" + JMID + "' and FMATERIALID= '" + FMATERIALID + "'";
                                            DataTable dt4;
                                            dt4 = DBUtils.ExecuteDataSet(this.Context, SQLselect4).Tables[0];

                                            if (dt4.Rows.Count > 0)
                                            {

                                                //如果之前价目表存在该数据 update
                                                string update1 = "update T_SAL_PRICELISTENTRY set FPRICE = '" + newFPRICE + "',FDOWNPRICE='" + newFPRICE + "'     " +
                                               "WHERE FID = '" + JMID + "' AND FMATERIALID= '" + FMATERIALID + "'";
                                                DBUtils.ExecuteDynamicObject(this.Context, update1);

                                            }
                                            else
                                            {
                                                // 如果之前价目表不存在该数据 insert

                                                //产生系统表格产生流水号 
                                                //避免 新增单据产生的FENTRYID 与 之前直接添加 FENTRYID 冲突
                                                string insertFENTRYID = "insert into z_SAL_PRICELISTENTRY (column1) values (1) ";
                                                DBUtils.ExecuteDynamicObject(this.Context, insertFENTRYID);

                                                //获取 FENTRYID
                                                string SQLFENTRYID = "select id from z_SAL_PRICELISTENTRY ";
                                                DataTable dtFENTRYID;
                                                dtFENTRYID = DBUtils.ExecuteDataSet(this.Context, SQLFENTRYID).Tables[0];

                                                int dtFENTRYIDcount = dtFENTRYID.Rows.Count;
                                                string newFENTRYID = dtFENTRYID.Rows[dtFENTRYIDcount - 1][0].ToString();

                                                //删除系统表格产生流水号 
                                                string deleteFENTRYID = "delete from z_SAL_PRICELISTENTRY ";
                                                DBUtils.ExecuteDynamicObject(this.Context, deleteFENTRYID);

                                                //获取销售价目表价格明细 FSEQ     FMATERIALTYPEID                   
                                                string SQLSelectFSEQ = "/*dialect*/SELECT  top 1 FSEQ ,FMATERIALTYPEID FROM T_SAL_PRICELISTENTRY where FID='" + JMID + "' ORDER BY FSEQ DESC ";
                                                DataTable dtFSEQ;
                                                dtFSEQ = DBUtils.ExecuteDataSet(this.Context, SQLSelectFSEQ).Tables[0];
                                                string FSEQ = dtFSEQ.Rows[0][0].ToString();
                                                string FMATERIALTYPEID = dtFSEQ.Rows[0][1].ToString();
                                                string newFSEQ = (double.Parse(FSEQ) + 1).ToString();

                                                //获取物料....
                                                String SQLSelectWL = @"select T1.FMATERIALID AS 物料ID,FAPPROVEDATE AS 审核日期, FNUMBER AS 物料编码,T2.FNAME AS 物料名称,FSPECIFICATION AS 规格型号,T4.FNAME AS 计价单位,FUNITID 
                                                    FROM T_BD_MATERIAL T1 LEFT JOIN T_BD_MATERIAL_L T2 
                                                    ON T2.FMATERIALID=T1.FMATERIALID LEFT JOIN t_BD_MaterialSale T3 
                                                    ON T3.FMATERIALID=T1.FMATERIALID LEFT JOIN T_BD_UNIT_L T4 
                                                    ON T4.FUNITID=T3.FSALEPRICEUNITID WHERE T1.FMATERIALID='" + FMATERIALID + "'";
                                                DataTable dtWL;
                                                dtWL = DBUtils.ExecuteDataSet(this.Context, SQLSelectWL).Tables[0];

                                                // 单位ID FUNITID：FUNITID
                                                string WLFUNITID = dtWL.Rows[0][6].ToString();

                                                //价格系数 ：JGXS
                                                double JGXS = 1;
                                                //失效 :  SX
                                                string SX = "A";
                                                //行审核状态 :  HSHZT
                                                string HSHZT = "A";
                                                //失效日期 :  ExpirationDate
                                                string ExpirationDate = "21000101";
                                                //添加对应数据
                                                string SQLinsert = "insert into T_SAL_PRICELISTENTRY (FENTRYID,FID,FSEQ,FMATERIALTYPEID,FMATERIALID,FPRICEUNITID,FPRICEBASE,FPRICE,FDOWNPRICE,FEFFECTIVEDATE,FEXPRIYDATE,FFORBIDSTATUS,FROWAUDITSTATUS,FUNITID)  " +
                                                    "values('" + newFENTRYID + "', '" + JMID + "', '" + newFSEQ + "', '" + FMATERIALTYPEID + "','" +
                                                    "" + FMATERIALID + "','" + WLFUNITID + "','" + JGXS + "','" + newFPRICE + "','" + newFPRICE + "'," +
                                                    "'" + EffectiveDate + "','" + ExpirationDate + "','" + SX + "','" + HSHZT + "','" + WLFUNITID + "') ";
                                                DBUtils.ExecuteDynamicObject(this.Context, SQLinsert);
                                            }
                                        }
                                        else
                                        {
                                            //throw new KDBusinessException("", "第" + (i + 1) + "行存在价目表调后含税单价为0！");
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                else
                {
                    //若存在重复值 警告
                    throw new KDBusinessException("", "存在重复物料！");
                }
            }
        }

    }

}

